﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		01/13/2011 1.0
-- Mask Testing: ? = Everything, ? = Nothing
-- =========================================================================================================

CREATE PROCEDURE [flcr].[api_search_list_sel]
	@MASK INT
	,@NAME	VARCHAR(8000) = NULL
	,@ACTIVE_FLG BIT = NULL
	,@TOPN INT = 32

AS
BEGIN

	CREATE TABLE #TEMP_LIST
		([ID] INT
		,[NAME] VARCHAR(8000)
		,[TYPE] INT
		,[ACTIVE_FLG] INT
		,[PARENT_ID] INT)
	
	DECLARE @DOMAIN	INT		= 1
			,@DEVICE	INT = 2
			,@SERVICE	INT = 4
			,@CONTRACT	INT	= 8
			,@SLA		INT = 16
			,@RESOURCE	INT = 32
			,@FACILITY  INT = 64
			,@ENV		INT = 128
			,@SUPPLIER	INT = 256
			,@PORTFOLIO	INT = 512
			,@PROJECT	INT = 1024
			,@CHANGE	INT = 2048
			,@RELEASE	INT = 4096
			,@ACCOUNT	INT	= 8192		

--=================================================================
-- BUILD RESULT SET BASED ON BITMASK PROVIDED
--=================================================================
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	IF (@MASK & @DOMAIN) = @DOMAIN
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG],[PARENT_ID]) 
		SELECT [DOMAIN_ID] AS [ID]
				,[DOMAIN_NAME] as [NAME]
				,@DOMAIN as [TYPE]
				,[ACTIVE_FLG]
				,[PARENT_ID] 
		  FROM flcr.[DOMAIN]
		  WHERE	[DOMAIN_NAME] LIKE COALESCE(@NAME,[DOMAIN_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])
	END

	IF (@MASK & @DEVICE) = @DEVICE
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG],[PARENT_ID]) 
		SELECT [DEVICE_ID] AS [ID]
				,[DEVICE_ASSET_NAME] as [NAME]
				,@DEVICE as [TYPE]
				,[ACTIVE_FLG]
				,[PARENT_ID] 
		  FROM flcr.[DEVICE]
		  WHERE	[DEVICE_ASSET_NAME] LIKE COALESCE(@NAME,[DEVICE_ASSET_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])
	END 

	IF (@MASK & @SERVICE) = @SERVICE
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG],[PARENT_ID]) 
		SELECT [SERVICE_ID] AS [ID]
				,[SERVICE_NAME] as [NAME]
				,@SERVICE as [TYPE]
				,[ACTIVE_FLG]
				,[PARENT_ID] 
		  FROM flcr.[SERVICE] 
		  WHERE	[SERVICE_NAME] LIKE COALESCE(@NAME,[SERVICE_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])
	END	   

	IF (@MASK & @CONTRACT) = @CONTRACT
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG],[PARENT_ID]) 
		SELECT [CONTRACT_ID] AS [ID]
				,[CONTRACT_NAME] as [NAME]
				,@CONTRACT as [TYPE]
				,[ACTIVE_FLG]
				,[PARENT_ID] 
		  FROM flcr.[CONTRACT] 
		  WHERE	[CONTRACT_NAME] LIKE COALESCE(@NAME,[CONTRACT_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])
	END

	IF (@MASK & @SLA) = @SLA
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG]) 
		SELECT [SLA_ID] AS [ID]
				,[SLA_NAME] as [NAME]
				,@SLA as [TYPE]
				,[ACTIVE_FLG]
		  FROM flcr.[SLA]  
		  WHERE	[SLA_NAME] LIKE COALESCE(@NAME,[SLA_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])
	END

	IF (@MASK & @RESOURCE) = @RESOURCE
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG],[PARENT_ID]) 
		SELECT [RESOURCE_ID] AS [ID]
				,[RESOURCE_NAME] as [NAME]
				,@RESOURCE as [TYPE]
				,[ACTIVE_FLG]
				,[PARENT_ID] 
		  FROM flcr.[RESOURCE]   
		  WHERE	[RESOURCE_NAME] LIKE COALESCE(@NAME,[RESOURCE_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])
	END

	IF (@MASK & @FACILITY) = @FACILITY
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG],[PARENT_ID]) 
		SELECT [FACILITY_ID] AS [ID]
				,[FACILITY_NAME] as [NAME]
				,@FACILITY as [TYPE]
				,[ACTIVE_FLG]
				,[PARENT_ID] 
		  FROM flcr.[FACILITY]    
		  WHERE	[FACILITY_NAME] LIKE COALESCE(@NAME,[FACILITY_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])
	END  

	IF (@MASK & @ENV) = @ENV
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG],[PARENT_ID]) 
		SELECT [ENV_ID] AS [ID]
				,[ENV_NAME] as [NAME]
				,@ENV as [TYPE]
				,[ACTIVE_FLG]
				,[PARENT_ID] 
		  FROM flcr.[ENVIRONMENT]     
		  WHERE	[ENV_NAME] LIKE COALESCE(@NAME,[ENV_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])
	END

	IF (@MASK & @PORTFOLIO) = @PORTFOLIO
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG],[PARENT_ID]) 
		SELECT [PORTFOLIO_ID] AS [ID]
				,[PORTFOLIO_NAME] as [NAME]
				,@PORTFOLIO as [TYPE]
				,[ACTIVE_FLG]
				,[PARENT_ID] 
		  FROM flcr.[PORTFOLIO]     
		  WHERE	[PORTFOLIO_NAME] LIKE COALESCE(@NAME,[PORTFOLIO_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])	   
	END
	
	IF (@MASK & @CHANGE) = @CHANGE
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG],[PARENT_ID]) 
		SELECT [CHANGE_ID] AS [ID]
				,[CHANGE_NAME] as [NAME]
				,@CHANGE as [TYPE]
				,[ACTIVE_FLG]
				,[PARENT_ID] 
		  FROM flcr.[CHANGE]     
		  WHERE	[CHANGE_NAME] LIKE COALESCE(@NAME,[CHANGE_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])	   
	END
	
	IF (@MASK & @RELEASE) = @RELEASE
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG],[PARENT_ID]) 
		SELECT [RELEASE_ID] AS [ID]
				,[RELEASE_NAME] as [NAME]
				,@RELEASE as [TYPE]
				,[ACTIVE_FLG]
				,[PARENT_ID] 
		  FROM flcr.[RELEASE]     
		  WHERE	[RELEASE_NAME] LIKE COALESCE(@NAME,[RELEASE_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])	   
	END
	
	IF (@MASK & @SUPPLIER) = @SUPPLIER
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG],[PARENT_ID]) 
		SELECT [SUPPLIER_ID] AS [ID]
				,[SUPPLIER_NAME] as [NAME]
				,@SUPPLIER as [TYPE]
				,[ACTIVE_FLG]
				,[PARENT_ID] 
		  FROM flcr.[SUPPLIER]     
		  WHERE	[SUPPLIER_NAME] LIKE COALESCE(@NAME,[SUPPLIER_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])	   
	END
	
	IF (@MASK & @ACCOUNT) = @ACCOUNT
	BEGIN
		INSERT INTO #TEMP_LIST ([ID], [NAME], [TYPE], [ACTIVE_FLG]) 
		SELECT [ACCT_ACCOUNT_ID] AS [ID]
				,[ACCOUNT_NAME] as [NAME]
				,@ACCOUNT as [TYPE]
				,[ACTIVE_FLG]
		  FROM flcr.[ACCT_ACCOUNT]     
		  WHERE	[ACCOUNT_NAME] LIKE COALESCE(@NAME,[ACCOUNT_NAME])
		    AND	[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG,[ACTIVE_FLG])	   
	END
--===================================================
-- RETURN RESULTS
--===================================================
	SELECT	TOP(@topN)
			[ID]
			,[NAME]
			,[TYPE]
			,[ACTIVE_FLG]
			,[PARENT_ID] 
	  FROM #TEMP_LIST
	 ORDER BY [NAME]

END